package edu.upenn.cis.cis550.hw3.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import edu.upenn.cis.cis550.hw3.client.QueryExport;
import edu.upenn.cis.cis550.hw3.server.RelationBuilder;
import edu.upenn.cis.cis550.hw3.shared.Relation;

/**
 * This class operates on the server (not the browser) and does a computation.
 * Its output will be returned to the browser.
 * 
 * @author zives
 * 
 */
public class QueryExportImpl extends RemoteServiceServlet implements
        QueryExport {
    Connection connection;

    private static final long serialVersionUID = 1L;

    /**
     * Constructor -- initialize our source tables and the object
     */
    public QueryExportImpl() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String serverName = "fling.seas.upenn.edu:3306";
            String database = "silviamo";
            String url = "jdbc:mysql://" + serverName + "/" + database;
            String user = "silviamo";
            String password = "dbproject";

            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }
    
    public Relation getAllUsersIDs() throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select userID from User")) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UsersIDS", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getUsersAddress(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select street, city, state, zip from Address where userID = " + userid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UserAddress", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getUsersPassword(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select password from Login where userID = " + userid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UserPassword", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getUsersInterests(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select I.interest from Interest I, UserInterest U where U.userID = " + userid +
                    		" and U.interestID = I.interestID")) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UserInterests", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getUsersPhotos(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select photoID, url, description, photoTime from Photo P where P.userID = " + userid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UserPhotos", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getUsersCircles(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select circleID, name from Circle where userID = " + userid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UsersCircles", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getCirclesFriends(String circleid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select friendID from Friend where circleName = \"" + circleid + "\"")) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("CirclesMembers", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getPhotoRating(String photoid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select R.userID AS ratorID, R.score from Rating R where R.photoID = " + photoid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("PhotosRatings", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getPhotoTags(String photoid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select tag from Tag where photoID = " + photoid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("PhotosTags", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Relation getPhotoComments(String photoid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select C.comment, C.userID AS comentorID, C.commentTime from Comment C where C.photoID = " + photoid)) {         	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("PhotosComments", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public void updateTable(String fileName, String tableName) throws IllegalArgumentException {
        try {
        	//LOAD XML LOCAL INFILE 'person.xml'
            //->   INTO TABLE person
            //->   ROWS IDENTIFIED BY '<person>';
            Statement statement = connection.createStatement();
            if (statement
                    .execute("LOAD DATA LOCAL INFILE '" + fileName + "' INTO TABLE " + tableName + 
                    		" FIELDS TERMINATED BY ',' " +
                    		" lines terminated by '\n'")) {         	
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
